package gov.nhso.prov.acc.apps.acc67;

import java.io.PrintWriter;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import gov.nhso.prov.acc.beans.Acc6AllocateBean;
import gov.nhso.prov.acc.beans.Acc6CrossNegativeYearBean;
import gov.nhso.prov.acc.beans.Acc6VenderBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.DigitUtil;
import gov.nhso.prov.acc.utils.Utility;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;

@SuppressWarnings("rawtypes")
public class Ajax extends Utility{
	
	public Ajax(HttpServletRequest request, HttpServletResponse response) {
		String job = this.getAttr(request, "job");
		if (job.equals("getAllocate")){
			this.getAllocate(request,response);
		} else if (job.equals("getVender")){
			this.getVender(request,response);
		} else if (job.equals("getProvince")){
			this.getProvince(request,response);
		} else if (job.equals("getVenderByProvinceId")){
			this.getVenderByProvinceId(request,response);
		} else if (job.equals("getAllocateCodeById")){
			this.getAllocateCodeById(request,response);
		} else if (job.equals("getRemain")){
			this.getRemain(request,response);
		} else if (job.equals("getRemainAcc7")){
			this.getRemainAcc7(request,response);
		} else if (job.equals("getRefNo")){
			this.getRefNo(request,response);
		} else if (job.equals("getAllocateLvl1")){
			this.getAllocateLvl1(request, response);
		} else if (job.equals("getAllocateLvl2")){
			this.getAllocateLvl2(request, response);
		} else if (job.equals("getChequeNo")){
			this.getChequeNo(request, response);
		} else if (job.equals("getVenderByProvinceIdInTrn")){
			this.getVenderByProvinceIdInTrn(request, response);
		} else if (job.equals("getVenderCodeByRefNo")) {
			this.getVenderCodeByRefNo(request, response);
		} else if (job.equals("getCodeCrossNegative")) {
			this.getCodeCrossNegative(request, response);
		} else if (job.equals("getProvinceIdByVenderCode")) {
			this.getProvinceIdByVenderCode(request, response);
		} else if (job.equals("getAllocateByRefNo")) {
			this.getAllocateByRefNo(request, response);
		}
	}
	
	private void getCodeCrossNegative(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String code = this.getAttr(request, "code");
			String oldCode = this.getAttr(request, "oldCode");
			StringBuffer result = new StringBuffer();
			List lsData = new ArrayList();
			Acc6CrossNegativeYearBean cny = new Acc6CrossNegativeYearBean();
			cny.setCode(code);
			lsData = dao.selectList(cny);
			result.append("[");
			result.append("{");
			if (lsData.size() > 0 && !oldCode.equals(code)) {
				result.append("\"result\" : \"1\"");
			} else {
				result.append("\"result\" : \"0\"");
			}
			result.append("}");
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getVenderByProvinceIdInTrn(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			StringBuffer sb = new StringBuffer();
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			sb.append("SELECT DISTINCT(VENDER_CODE) AS VENDER_CODE FROM "+this.schema+"ACC6_TRANSACTION ");
			sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND  TRANSACTION_TYPE = 'C' AND BUDGET_YEAR = "+budgetYear+" ");
			List ls = dao.listDataSql(sb.toString());
			StringBuffer result = new StringBuffer();
			result.append("[");
			for(int i=0;i<ls.size();i++){
				Map map = (Map)ls.get(i);
				if(i > 0){
					result.append(",");
				}
				result.append("{");
				result.append("\"code\" : \""+map.get("VENDER_CODE")+"\",");
				result.append("}");
			}
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getAllocate(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String budgetYear = this.getAttr(request, "budgetYear");
			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			List allocateList = null;
			
			allocateBean.setBudgetYear(budgetYear);
			allocateBean.setAllocateApply("Y");
			allocateBean.setActive("Y");
			allocateList = dao.selectList(allocateBean ," ORDER BY ALLOCATE_SEQ ASC ");
			StringBuffer result = new StringBuffer();
			result.append("[");
			for(int i=0;i<allocateList.size();i++){
				allocateBean = (Acc6AllocateBean) allocateList.get(i);
				if(i > 0){
					result.append(",");
				}
				result.append("{");
				result.append("\"code\" : \""+allocateBean.getAllocateCode()+"\",");
				result.append("\"name\" : \""+allocateBean.getAllocateName()+"\"");
				result.append("}");
			}
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private void getAllocateLvl1(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String budgetYear = this.getAttr(request, "budgetYear");
			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			List allocateList = null;
			
			allocateBean.setBudgetYear(budgetYear);
			allocateBean.setAllocateLevel("1");
			allocateList = dao.selectList(allocateBean, " ORDER BY ALLOCATE_SEQ ASC");
			StringBuffer result = new StringBuffer();
			result.append("[");
			for(int i=0;i<allocateList.size();i++){
				allocateBean = (Acc6AllocateBean) allocateList.get(i);
				if(i > 0){
					result.append(",");
				}
				result.append("{");
				result.append("\"code\" : \""+allocateBean.getAllocateCode()+"\",");
				result.append("\"name\" : \""+allocateBean.getAllocateName()+"\"");
				result.append("}");
			}
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
		
	private void getAllocateLvl2(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String budgetYear = this.getAttr(request, "budgetYear");
			String allocateCode = this.getAttr(request, "allocateCode");
			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			List allocateList = null;
			
			allocateBean.setBudgetYear(budgetYear);
			allocateBean.setAllocateLevel("2");
			allocateBean.setParentCode(allocateCode);
			allocateList = dao.selectList(allocateBean, " ORDER BY ALLOCATE_SEQ ASC");
			allocateList = dao.selectList(allocateBean);
			StringBuffer result = new StringBuffer();
			result.append("[");
			for(int i=0;i<allocateList.size();i++){
				allocateBean = (Acc6AllocateBean) allocateList.get(i);
				if(i > 0){
					result.append(",");
				}
				result.append("{");
				result.append("\"code\" : \""+allocateBean.getAllocateCode()+"\",");
				result.append("\"name\" : \""+allocateBean.getAllocateName()+"\"");
				result.append("}");
			}
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getVender(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String provinceId = this.getAttr(request, "provinceCode");
			Acc6VenderBean venderBean = new Acc6VenderBean();
			List venderList = null;
			
			venderBean.setProvinceId(provinceId);
			venderBean.setActive("Y");
			venderList = dao.selectList(venderBean, " AND VENDER_ACCOUNT IS NOT NULL");
			StringBuffer result = new StringBuffer();
			result.append("[");
			for(int i=0;i<venderList.size();i++){
				venderBean = (Acc6VenderBean) venderList.get(i);
				if(i > 0){
					result.append(",");
				}
				result.append("{");
				result.append("\"code\" : \""+venderBean.getVenderCode()+"\",");
				result.append("\"name\" : \""+venderBean.getVenderName()+"\"");
				result.append("}");
			}
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
	}

	private void getProvince(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List provinceList = new ArrayList();
			Map map = null;
			
			provinceList = this.dataUtil(dao).lsProvince();
			StringBuffer result = new StringBuffer();
			result.append("[");
			for(int i=0;i<provinceList.size();i++){
				map = (Map)provinceList.get(i);
				if(i > 0){
					result.append(",");
				}
				result.append("{");
				result.append("\"code\" : \""+map.get("PROVINCE_ID")+"\",");
				result.append("\"name\" : \""+map.get("PROVINCE_NAME")+"\"");
				result.append("}");
			}
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getVenderByProvinceId(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String provinceId = this.getAttr(request, "provinceId");
			String venderType = this.getAttr(request, "venderType");
			Acc6VenderBean venderBean = new Acc6VenderBean();
			List venderList = null;
			
			venderBean.setProvinceId(provinceId);
			venderBean.setActive("Y");
			if (venderType != null && venderType.equals("T")){
				venderList = dao.selectList(venderBean, " AND VENDER_TYPE <> 'C' AND VENDER_ACCOUNT IS NOT NULL");
			} else if (venderType != null && venderType.equals("C")){
				venderBean.setVenderType(venderType);
				venderList = dao.selectList(venderBean);
			}
			StringBuffer result = new StringBuffer();
			result.append("[");
			for(int i=0;i<venderList.size();i++){
				venderBean = (Acc6VenderBean) venderList.get(i);
				if(i > 0){
					result.append(",");
				}
				result.append("{");
				result.append("\"code\" : \""+venderBean.getVenderCode()+"\",");
				result.append("\"name\" : \""+venderBean.getVenderName()+"\",");
				if (venderType != null && venderType.equals("T")){
					result.append("\"venderAccount\" : \""+dao.chkData(venderBean.getVenderAccount().trim())+"\"");
				} else if (venderType != null && venderType.equals("C")){
					result.append("\"venderAccount\" : \""+venderType+"\"");
				}
				result.append("}");
			}
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getAllocateCodeById(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String refNo = this.getAttr(request, "refNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String venderCode = this.getAttr(request, "venderCode");
			String allocateCode = this.getAttr(request, "allocateCode");
			String sql = "";
			Map map = null;
			sql = "SELECT ACA.ALLOCATE_CODE,ACA.ALLOCATE_NAME,SUM(NVL(ACT.EXPENSE_AMOUNT,0))- SUM(NVL(ACT.INCOME_AMOUNT,0)) AS REMAIN, ACT.CATEGORY_ID,ACV.VENDER_ACCOUNT,ACV.VENDER_NAME "+
			"FROM ACC6_TRANSACTION ACT,ACC6_ALLOCATE ACA,ACC6_VENDER ACV "+
			"WHERE ACT.REF_NO = '"+ URLDecoder.decode(refNo,"UTF-8") +"' AND ACT.ALLOCATE_CODE = ACA.ALLOCATE_CODE AND ACT.VENDER_CODE = ACV.VENDER_CODE "+
			" AND ACT.IS_BORROW = 'Y' AND ACT.REASON_CODE = '00' " +
			" AND ACT.TRANSACTION_TYPE IN ('T','C','R') AND ACT.IS_RETURN = 'N' AND ACT.PROVINCE_ID = '"+provinceId+"' AND ACT.BUDGET_YEAR = '"+budgetYear+"' " +
			" AND ACV.VENDER_CODE = '" + venderCode + "' AND ACA.ALLOCATE_CODE = '" + allocateCode + "'" + 
			"GROUP BY ACA.ALLOCATE_CODE,ACA.ALLOCATE_NAME,ACT.CATEGORY_ID,ACV.VENDER_ACCOUNT,ACV.VENDER_NAME ";
			
			map = dao.findDataSql(sql);
			StringBuffer result = new StringBuffer();
			
			if(map.size()>0){
				result.append("[");
				result.append("{");
				result.append("\"catId\" : \""+map.get("CATEGORY_ID")+"\",");
				result.append("\"remain\" : \""+map.get("REMAIN")+"\",");
				result.append("\"result\" : \"1\"");
				result.append("}");
				result.append("]");
			}else{
				result.append("[");
				result.append("{");
				result.append("\"result\" : \"0\"");
				result.append("}");
				result.append("]");
			}

			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private void getRemain(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String allocateCode = this.getAttr(request, "allocateCode").trim();
			String nagative = "";
			String apply = "";
			String code = "";
			double remain = 0d;
			double sumAmount  = 0d;
			String sql = "";
			String allocateCodeSub = allocateCode.substring(2, 4);
			Map map = new HashMap();
			Map map2 = new HashMap();
			
			sql  = "SELECT NVL(NEGATIVE,'') AS NEGATIVE "+
			"FROM " + this.schema + ".ACC6_ALLOCATE "+
			"WHERE ALLOCATE_CODE = '"+allocateCode+"' ";
			map = dao.findDataSql(sql);
			nagative = dao.chkData(map.get("NEGATIVE"));
			
			sql = "SELECT * FROM "+ this.schema + ".ACC6_CROSS_NEGATIVE_YEAR WHERE CODE = '" + allocateCodeSub + "' ";
			map = dao.findDataSql(sql);
			apply = dao.chkData(map.get("APPLY"));
			code = dao.chkData(map.get("CODE"));
			
			List lsby = this.dataUtil(dao).lsBudgetYear();
			String sql01 = " AND (";
			String sql02 = " AND (";
			String sql03 = " AND (";
			String sql04 = " AND (";
			String by = "";
			for (int n = 0; n < lsby.size(); n++) {
				by = ((Map) lsby.get(n)).get("BUDGET_YEAR").toString();
				if (n == 0) {
					sql01 += " ALLOCATE_CODE LIKE '" + by.substring(2, 4) + code + "%' ";
					sql02 += " AET.ALLOCATE_CODE LIKE '" + by.substring(2, 4) + code + "%' ";
					sql03 += " AEC.ALLOCATE_CODE LIKE '" + by.substring(2, 4) + code + "%' ";
					sql04 += " AED.ALLOCATE_CODE LIKE '" + by.substring(2, 4) + code + "%' ";
				} else {
					sql01 += " OR ALLOCATE_CODE LIKE '" + by.substring(2, 4) + code + "%' ";
					sql02 += " OR AET.ALLOCATE_CODE LIKE '" + by.substring(2, 4) + code + "%' ";
					sql03 += " OR AEC.ALLOCATE_CODE LIKE '" + by.substring(2, 4) + code + "%' ";
					sql04 += " OR AED.ALLOCATE_CODE LIKE '" + by.substring(2, 4) + code + "%' ";
				}
			}
			sql01 += ") ";
			sql02 += ") ";
			sql03 += ") ";
			sql04 += ") ";
			
			/*List lsby = this.dataUtil(dao).lsBudgetYear();
			String sql01 = " AND (";
			String sql02 = " AND (";
			String sql03 = " AND (";
			String by = "";
			for (int n = 0; n < lsby.size(); n++) {
				by = ((Map) lsby.get(n)).get("BUDGET_YEAR").toString();
				if (n == 0) {
					sql01 += " ALLOCATE_CODE LIKE '" + by.substring(2, 4) + "01%' ";
					sql02 += " AET.ALLOCATE_CODE LIKE '" + by.substring(2, 4) + "01%' ";
					sql03 += " AEC.ALLOCATE_CODE LIKE '" + by.substring(2, 4) + "01%' ";
				} else {
					sql01 += " OR ALLOCATE_CODE LIKE '" + by.substring(2, 4) + "01%' ";
					sql02 += " OR AET.ALLOCATE_CODE LIKE '" + by.substring(2, 4) + "01%' ";
					sql03 += " OR AEC.ALLOCATE_CODE LIKE '" + by.substring(2, 4) + "01%' ";
				}
			}
			sql01 += ") ";
			sql02 += ") ";
			sql03 += ") ";*/

			if (allocateCodeSub.equals(code) && apply.equals("Y")) {
			//if (allocateCodeSub.equals("01")) {
				sql = "SELECT NVL(SUM(NVL(INCOME_AMOUNT,0))- SUM(NVL(EXPENSE_AMOUNT,0)),0) AS REMAIN "+
				"FROM ACC6_TRANSACTION "+
				"WHERE PROVINCE_ID = '"+provinceId+"' "+
				" " + sql01 + " AND (REASON_CODE = '00' OR REASON_CODE IS NULL)";
				map = dao.findDataSql(sql);
				if (map.get("REMAIN") != null && !map.get("REMAIN").equals("")){
					remain = Double.parseDouble(map.get("REMAIN").toString());
				}
				sql = "SELECT NVL(SUM(AET.TRANSFER_AMOUNT),0) AS SUM_AMOUNT "+
				"FROM ACC6_EXPENSE AE, ACC6_EXPENSE_TRANSFER AET "+
				"WHERE AE.DOC_NO = AET.DOC_NO " + sql02 +
				"AND AE.PROVINCE_ID = '"+provinceId+"' AND AET.ACTIVE = 'Y' "+
				"AND AET.TRANSACTION_TIMESTAMP IS NULL ";
				map2 = dao.findDataSql(sql);
				sumAmount += Double.parseDouble(map2.get("SUM_AMOUNT").toString());
				
				sql = "SELECT NVL(SUM(AEC.CHEQUE_AMOUNT),0) AS SUM_AMOUNT "+
				"FROM ACC6_EXPENSE AE, ACC6_EXPENSE_CHEQUE AEC "+
				"WHERE AE.DOC_NO = AEC.DOC_NO " + sql03 +
				"AND AE.PROVINCE_ID = '"+provinceId+"' AND AEC.ACTIVE = 'Y' "+
				"AND AEC.TRANSACTION_TIMESTAMP IS NULL ";
				map2 = dao.findDataSql(sql);
				sumAmount += Double.parseDouble(map2.get("SUM_AMOUNT").toString());
				
				sql = "SELECT NVL(SUM(AED.CHEQUE_AMOUNT),0) AS SUM_AMOUNT "+
				"FROM ACC6_CHEQUE AC, ACC6_CHEQUE_DETAIL AED "+
				"WHERE AC.DOC_NO = AED.DOC_NO " + sql04 +
				"AND AC.PROVINCE_ID = '"+provinceId+"' AND AED.ACTIVE = 'Y' "+
				"AND AED.TRANSACTION_TIMESTAMP IS NULL ";
				map2 = dao.findDataSql(sql);
				sumAmount += Double.parseDouble(map2.get("SUM_AMOUNT").toString());
				
			} else {
				
				sql = "SELECT NVL(SUM(NVL(INCOME_AMOUNT,0))- SUM(NVL(EXPENSE_AMOUNT,0)),0) AS REMAIN "+
				"FROM ACC6_TRANSACTION "+
				"WHERE BUDGET_YEAR = '"+budgetYear+"' AND PROVINCE_ID = '"+provinceId+"' "+
				"AND ALLOCATE_CODE = '"+allocateCode+"' AND (REASON_CODE = '00' OR REASON_CODE IS NULL)";
				map = dao.findDataSql(sql);
				
				if (map.get("REMAIN") != null && !map.get("REMAIN").equals("")){
					remain = Double.parseDouble(map.get("REMAIN").toString());
				}
				
				sql = "SELECT NVL(SUM(AET.TRANSFER_AMOUNT),0) AS SUM_AMOUNT "+
				"FROM ACC6_EXPENSE AE, ACC6_EXPENSE_TRANSFER AET "+
				"WHERE AE.DOC_NO = AET.DOC_NO AND AET.ALLOCATE_CODE = '"+allocateCode+"' "+
				"AND AE.PROVINCE_ID = '"+provinceId+"' AND AE.BUDGET_YEAR = '"+budgetYear+"' AND AET.ACTIVE = 'Y' "+
				"AND AET.TRANSACTION_TIMESTAMP IS NULL ";
				map2 = dao.findDataSql(sql);
				sumAmount += Double.parseDouble(map2.get("SUM_AMOUNT").toString());
				
				sql = "SELECT NVL(SUM(AEC.CHEQUE_AMOUNT),0) AS SUM_AMOUNT "+
				"FROM ACC6_EXPENSE AE, ACC6_EXPENSE_CHEQUE AEC "+
				"WHERE AE.DOC_NO = AEC.DOC_NO AND AEC.ALLOCATE_CODE = '"+allocateCode+"' "+
				"AND AE.PROVINCE_ID = '"+provinceId+"' AND AE.BUDGET_YEAR = '"+budgetYear+"' AND AEC.ACTIVE = 'Y' "+
				"AND AEC.TRANSACTION_TIMESTAMP IS NULL ";
				map2 = dao.findDataSql(sql);
				sumAmount += Double.parseDouble(map2.get("SUM_AMOUNT").toString());

				sql = "SELECT NVL(SUM(AED.CHEQUE_AMOUNT),0) AS SUM_AMOUNT "+
				"FROM ACC6_CHEQUE AC, ACC6_CHEQUE_DETAIL AED "+
				"WHERE AC.DOC_NO = AED.DOC_NO AND AED.ALLOCATE_CODE = '"+allocateCode+"' "+
				"AND AC.PROVINCE_ID = '"+provinceId+"' AND AED.ACTIVE = 'Y' "+
				"AND AED.TRANSACTION_TIMESTAMP IS NULL ";
				map2 = dao.findDataSql(sql);
				sumAmount += Double.parseDouble(map2.get("SUM_AMOUNT").toString());
				
			}
			
			dao.printMessage();
			StringBuffer result = new StringBuffer();
			DigitUtil du = new DigitUtil();
			
			remain = remain - sumAmount;
			
			if(nagative != null && !nagative.equals("")){
				result.append("[");
				result.append("{");
				result.append("\"negative\" : \""+nagative+"\",");
				result.append("\"remain\" : \""+du.formatDecimal(remain, 2)+"\",");
				result.append("\"result\" : \"1\"");
				result.append("}");
				result.append("]");
			}else{
				result.append("[");
				result.append("{");
				result.append("\"result\" : \"0\"");
				result.append("}");
				result.append("]");
			}
			//dao.printMessage();
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			dao.printMessage();
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				//dao.printMessage();
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getRemainAcc7(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String sql = "";
			Map map = null;
			sql = "SELECT SUM(NVL(INCOME_AMOUNT,0))- SUM(NVL(EXPENSE_AMOUNT,0)) AS REMAIN "+
			"FROM ACC7_TRANSACTION "+
			"WHERE BUDGET_YEAR = '"+budgetYear+"' AND PROVINCE_ID = '"+provinceId+"' ";
			map = dao.findDataSql(sql);
			StringBuffer result = new StringBuffer();
			
			if(map.size()>0){
				result.append("[");
				result.append("{");
				result.append("\"remain\" : \""+map.get("REMAIN")+"\",");
				result.append("\"result\" : \"1\"");
				result.append("}");
				result.append("]");
			}else{
				result.append("[");
				result.append("{");
				result.append("\"result\" : \"0\"");
				result.append("}");
				result.append("]");
			}

			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private void getRefNo(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List list = new ArrayList();
			
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			
			String sql = "";
			Map map = new HashMap();
			sql = "SELECT DISTINCT  REF_NO "+
			"FROM ACC6_TRANSACTION "+
			"WHERE IS_BORROW = 'Y' AND REASON_CODE = '00' AND PROVINCE_ID = '"+provinceId+"' "+
			"AND BUDGET_YEAR = '"+budgetYear+"' AND IS_RETURN = 'N' AND TRANSACTION_TYPE IN ('T','C') ";
			list = dao.listDataSql(sql);
					
			StringBuffer result = new StringBuffer();
			
			result.append("[");
			for(int i=0;i<list.size();i++){
				map = (Map)list.get(i);
				if(i > 0){
					result.append(",");
				}
				result.append("{");
				result.append("\"refNo\" : \""+map.get("REF_NO")+"\"");
				result.append("}");
			}
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getChequeNo(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String chequeNo = this.getAttr(request, "chequeNo");
			String provinceId = this.getAttr(request, "provinceId");
			StringBuffer result = new StringBuffer();
			StringBuffer sql = new StringBuffer();
			Map map = new HashMap();
			int countResult = 0;
			sql.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO ");
			sql.append("FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE ");
			sql.append("WHERE CHEQUE_NO = '" + chequeNo + "' AND SUBSTR(DOC_NO,10,4) = '"+provinceId+"' AND ACTIVE = 'Y' ");
			map = dao.findDataSql(sql.toString());
			countResult = Integer.parseInt(dao.chkData(map.get("COUNT_CHEQUE_NO")));
			
			sql.setLength(0);
			sql.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO ");
			sql.append("FROM " + this.schema + ".ACC6_CHEQUE ");
			sql.append("WHERE CHEQUE_NO = '" + chequeNo + "' AND SUBSTR(DOC_NO,10,4) = '"+provinceId+"' AND ACTIVE = 'Y' ");
			map = dao.findDataSql(sql.toString());
			countResult += Integer.parseInt(dao.chkData(map.get("COUNT_CHEQUE_NO")));
			
			result.append("[");
				if (countResult > 0) {
					result.append("{");
					result.append("\"result\" : \"1\"");
					result.append("}");
				} else {
					result.append("{");
					result.append("\"result\" : \"0\"");
					result.append("}");
				}
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getVenderCodeByRefNo(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String refNo = this.getAttr(request, "refNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			StringBuffer result = new StringBuffer();
			StringBuffer sql = new StringBuffer();
			List list = new ArrayList();
			sql.append("SELECT AVB.VENDER_ACCOUNT,AVB.VENDER_NAME,ACT.VENDER_CODE ");
			sql.append("FROM "+this.schema+".ACC6_TRANSACTION ACT INNER JOIN "+this.schema+".ACC6_VENDER AVB ON ACT.VENDER_CODE = AVB.VENDER_CODE ");
			sql.append("WHERE ACT.REF_NO = '" + URLDecoder.decode(refNo,"UTF-8") + "' AND ACT.PROVINCE_ID = '" + provinceId + "' AND ACT.TRANSACTION_TYPE IN ('T','C') ");
			sql.append("AND ACT.BUDGET_YEAR = '" + budgetYear + "' AND ACT.IS_BORROW = 'Y' AND ACT.IS_RETURN ='N' ");
			list = dao.listDataSql(sql.toString());
			result.append("[");
			if (list.size() > 0) {
				for (int i = 0 ; i < list.size() ; i++) {
					Map map = (Map) list.get(i);
					result.append("{");
					result.append("\"result\" : \"1\",");
					result.append("\"venderAccount\" : \"" + dao.chkData(map.get("VENDER_ACCOUNT")) + "\",");
					result.append("\"venderName\" : \"" + dao.chkData(map.get("VENDER_NAME")) + "\",");
					result.append("\"venderCode\" : \"" + dao.chkData(map.get("VENDER_CODE")) + "\"");
					result.append("}");
					if (i < list.size()-1) {
						result.append(",");
					}
				}
			} else {
				result.append("{");
				result.append("\"result\" : \"0\"");
				result.append("}");
			}
			result.append("]");
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getProvinceIdByVenderCode(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String venderCode = this.getAttr(request, "venderCode");
			StringBuffer result = new StringBuffer();
			StringBuffer sql = new StringBuffer();
			Map map = new HashMap();
			sql.append("SELECT PROVINCE_ID,NVL(VENDER_ACCOUNT,'') AS VENDER_ACCOUNT,VENDER_NAME ");
			sql.append("FROM " + this.schema + ".ACC6_VENDER ");
			sql.append("WHERE VENDER_CODE = '" + venderCode + "' ");
			map = dao.findDataSql(sql.toString());
			String provinceId = dao.chkData(map.get("PROVINCE_ID"));
			String venderAccount = dao.chkData(map.get("VENDER_ACCOUNT")).trim();
			String venderName = dao.chkData(map.get("VENDER_NAME"));
			result.append("[");
			if (!provinceId.equals("")) {
				result.append("{");
				result.append("\"result\" : \"1\",");
				result.append("\"provinceId\" : \"" + provinceId + "\",");
				result.append("\"venderName\" : \"" + venderName + "\",");
				result.append("\"venderAccount\" : \"" + venderAccount + "\"");
				result.append("}");
			} else {
				result.append("{");
				result.append("\"result\" : \"0\"");
				result.append("}");
			}
			result.append("]");
			
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void getAllocateByRefNo(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String refNo = this.getAttr(request, "refNo");
			String venderCode = this.getAttr(request, "venderCode");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			StringBuffer result = new StringBuffer();
			StringBuffer sql = new StringBuffer();
			List list = new ArrayList();
			Map map = new HashMap();
			sql.append("SELECT ALLOCATE_CODE ");
			sql.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
			sql.append("WHERE REF_NO = '" + URLDecoder.decode(refNo,"UTF-8") + "' AND VENDER_CODE = '" + venderCode + "' AND PROVINCE_ID = '" + provinceId + "' ");
			sql.append("AND BUDGET_YEAR = '" + budgetYear + "' ");
			list = dao.listDataSql(sql.toString());
			result.append("[");
			if (list.size() > 0) {
				for (int i = 0; i < list.size() ; i++) {
					map = (Map) list.get(i);
					String allocateCode = dao.chkData(map.get("ALLOCATE_CODE"));
					result.append("{");
					result.append("\"result\" : \"1\",");
					result.append("\"allocateCode\" : \"" + allocateCode + "\",");
					result.append("\"allocateName\" : \"" + this.dataUtil(dao).getAllocateById(allocateCode) + "\"");
					result.append("}");
					if (i < list.size()-1) {
						result.append(",");
					}
				}
			} else {
				result.append("{");
				result.append("\"result\" : \"0\"");
				result.append("}");
			}
			
			result.append("]");
			
			response.setContentType("text/html");
	        response.setHeader("Cache-control", "no-cache, no-store");
	        response.setHeader("Pragma", "no-cache");
	        response.setHeader("Expires", "-1");
	        response.setCharacterEncoding("windows-874");
			PrintWriter out = response.getWriter();
			out.println(result);
			out.flush();
			out.close();
			conn.commit();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
